package _3_0dao;


import java.sql.*;
import java.util.*;


import _3_0.pojo.Classes;
import _3_0.utils.JDBCUtils;

public class ClassesDAO {
	
	// DAO 负责提供增删改查的功能

	// 增加
	public void insert(Classes c) throws SQLException{
		
		String sql = "insert into classes values (?,?)";
		// 1. 得到连接
		Connection conn = JDBCUtils.getConnection();
		// 2. 创建查询对象
		PreparedStatement stmt = conn.prepareStatement(sql);
		stmt.setInt(1, c.getId());
		stmt.setString(2, c.getName());
		// 3. 执行
		int result = stmt.executeUpdate();
		System.out.println("影响行数:"+result);
		// 4. 释放资源
		JDBCUtils.destory(conn, stmt, null);
	};
	
	// 删除
	public void deleteById(int id) throws SQLException{
		String sql = "delete from classes where id = ?";
		// 1. 得到连接
		Connection conn = JDBCUtils.getConnection();
		// 2. 创建查询对象
		PreparedStatement stmt = conn.prepareStatement(sql);
		stmt.setInt(1, id);
		// 3. 执行
		int result = stmt.executeUpdate();
		System.out.println("影响行数:"+result);
		// 4. 释放资源
		JDBCUtils.destory(conn, stmt, null);
	};
	
	// 修改
	public void update(Classes c) throws SQLException{
		
		String sql = "update classes set name = ? where id = ?";
		// 1. 得到连接
		Connection conn = JDBCUtils.getConnection();
		// 2. 创建查询对象
		PreparedStatement stmt = conn.prepareStatement(sql);
		stmt.setString(1, c.getName());
		stmt.setInt(2, c.getId());
		// 3. 执行
		int result = stmt.executeUpdate();
		System.out.println("影响行数:"+result);
		// 4. 释放资源
		JDBCUtils.destory(conn, stmt, null);
	};
	
	// 查询
	public Classes findById(int id) throws SQLException{
		String sql = "select * from classes where id = ?";
		// 1. 得到连接
		Connection conn = JDBCUtils.getConnection();
		// 2. 创建查询对象
		PreparedStatement stmt = conn.prepareStatement(sql);
		stmt.setInt(1, id);
		// 3. 执行
		ResultSet rs = stmt.executeQuery();
		Classes c = null;
		if(rs.next())
		{
			String name = rs.getString(2);
			c = new Classes(id,name);
		}
		
		// 4. 释放资源
		JDBCUtils.destory(conn, stmt, rs);
		
		return c;
	}
	
	// 查询所有
	public List<Classes> findAll() throws SQLException{
		
		String sql = "select * from classes";
		// 1. 得到连接
		Connection conn = JDBCUtils.getConnection();
		// 2. 创建查询对象
		PreparedStatement stmt = conn.prepareStatement(sql);
		// 3. 执行
		ResultSet rs = stmt.executeQuery();
		List<Classes> list = new ArrayList<>();
		while(rs.next())
		{
			int id = rs.getInt(1);
			String name = rs.getString(2);
			Classes c = new Classes(id,name);
			list.add(c);
		}
		
		// 4. 释放资源
		JDBCUtils.destory(conn, stmt, rs);
		
		return list;
		
	};
}